package cn.edu.cug.cs.gtl.docsrv.dict.dao.imp;

import cn.edu.cug.cs.gtl.docsrv.config.Configuration;
import cn.edu.cug.cs.gtl.docsrv.dict.dao.TableDao;
import cn.edu.cug.cs.gtl.common.Pair;
import cn.edu.cug.cs.gtl.mybatis.Session;
import cn.edu.cug.cs.gtl.mybatis.mapper.common.SelectMapper;
import cn.edu.cug.cs.gtl.mybatis.mapper.dict.DictColInfo;
import cn.edu.cug.cs.gtl.mybatis.mapper.dict.DictColInfoMapper;
import cn.edu.cug.cs.gtl.mybatis.metadata.Constant;
import cn.edu.cug.cs.gtl.mybatis.metadata.DictUtils;
import cn.edu.cug.cs.gtl.protos.TableInfo;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Component
public class TableDaoImpl implements TableDao {
    @Override
    public List<Map> getTableStructureV1(String tableName) throws Exception {
        List<Map> columns = new ArrayList<>();
        try {
            List<DictColInfo> dictColInfos = getTableStructure(tableName);
            if(dictColInfos==null) return columns;
            for(DictColInfo rs: dictColInfos) {
                Map<String, Object> map = new LinkedHashMap<>();
                map.put("columnName", rs.getColEnName());
                map.put("columnComment", rs.getColCnName());
                String dataType = rs.getColType().toUpperCase();
                if (dataType.equals("BLOB")) {
                    map.put("dataType", "BLOB");
                } else if (dataType.equals("DATE") || dataType.equals("TIMESTAMP")){
                    map.put("dataType", "DATE");
                } else {
                    map.put("dataType", dataType);
                }
                columns.add(map);

            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return columns;
    }

    @Override
    public List<DictColInfo> getTableStructure(String tableName) throws Exception {
        try {
            Session s = Configuration.getSession();
            DictColInfoMapper dictColInfoMapper = s.getMapper(DictColInfoMapper.class);
            List<DictColInfo> l= dictColInfoMapper.selectByTabEnName(tableName);
            s.close();
            return l;
        } catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public TableInfo getTableInfo(String tableName) throws Exception {
        try {
            Session s = Configuration.getSession();
            DictUtils dictUtils = new DictUtils(s);
            TableInfo tableInfo= dictUtils.getTableInfo(tableName);
            s.close();
            return tableInfo;
        } catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 根据传入的数据表信息，构建SQL语句，返回查询结果
     * @param tableName 数据表的名称
     * @param pageNumber   页码，从1开始
     * @param pageSize     页面大小，也即每页中最多存放的记录条数
     * @param tableStructure 数据表的结构信息
     * @return 返回数据表中的数据记录
     * @throws Exception
     */
    @Override
    public List<Map<String,Object>> getTableData(String tableName, Integer pageNumber, Integer pageSize, List<Map> tableStructure) throws Exception {
        try {
            Session s = Configuration.getSession();
            s.clearCache();
            DictUtils dictUtils = new DictUtils(s);
            List<Map<String,Object>> l= dictUtils.selectPage(tableName, tableStructure, pageNumber,pageSize);
            s.close();
            return l;
        } catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

/**
     * 根据传入的数据表信息，构建SQL语句，返回查询结果
     * @param tableName 数据表的名称
     * @param pageNumber   页码，从1开始
     * @param pageSize     页面大小，也即每页中最多存放的记录条数
     * @param tableStructure 数据表的结构信息
     * @return 返回数据表中的数据记录
     * @throws Exception
     */
    @Override
    public List<Map> getTableDataV1(String tableName, Integer pageNumber, Integer pageSize, List<Map> tableStructure) throws Exception {
//
//        ### SQL: select RAW_INTERNAL_ID, RAW_EN_TITLE, RAW_CN_TITLE, RAW_UUID, RAW_VERSION, RAW_KEYWORDS, RAW_ABSTRACT, RAW_CONTENT, RAW_TYPE, RAW_AUTHORS, RAW_AFFILIATION, RAW_TAGS, RAW_URL, to_char(RAW_APPEND_DATE, 'yyyy-mm-dd hh24:mi:ss') as RAW_APPEND_DATE, to_char(RAW_UPDATE_DATE, 'yyyy-mm-dd hh24:mi:ss') as RAW_UPDATE_DATE, to_char(RAW_CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') as RAW_CREATE_DATE, RAW_MEMO, RAW_CATALOG, nvl2(RAW_RAW_BYTES,1,0) as RAW_RAW_BYTES from  (SELECT rownum no, RAW_ALL.* from RAW_ALL WHERE rownum <= 16) WHERE no >= 1
//### Cause: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such column: rownum)
//        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
//        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:149)
//        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
//        at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
//        at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
//        at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
//        at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
//        at com.sun.proxy.$Proxy76.query(Unknown Source)
//        at cn.edu.cug.cs.gtl.docsrv.dict.dao.imp.TableDaoImpl.getTableDataV1(TableDaoImpl.java:96)
//        在Oracle中中正常，但是在SQLITE中，系统没有rownum字段，该如何处理？
        List<Map> data = new ArrayList<>();
        try {
            String sql = Constant.GET_TABLE_DATA + buildSelectSQL(tableStructure) + " from ";
            sql += " (SELECT rownum no, " + tableName + ".* from " + tableName + " WHERE rownum <= " + (pageNumber * pageSize) + ")" +
                    " WHERE no >= " + ((pageNumber - 1) * pageSize + 1);
            Session s = Configuration.getSession();
            s.clearCache();
            SelectMapper selectMapper = s.getMapper(SelectMapper.class);
            List<LinkedHashMap<String,Object>> rsList = selectMapper.query(sql);
            s.close();
            for (LinkedHashMap<String,Object> rs: rsList) {
                Map<String, String> map = new LinkedHashMap<>();
                for (Map columnStructure : tableStructure) {
                    String a = (String) columnStructure.get("columnName");
                    //这里只要判断是不是BLOB，因为前面获取表结构的时候已经将blob、BLOB、longblob、LONGBLOB都转成了BLOB
                    switch((String) columnStructure.get("dataType")) {
                        case "BLOB":{
                            Object obj = rs.get(a);
                            if(obj==null)
                                map.put(a,"");
                            else {
                                BigDecimal bigDecimal = (BigDecimal)obj;
                                if(bigDecimal.intValue()==0)
                                    map.put(a,"");
                                else
                                    map.put(a, "BLOB");
                            }
                            break;
                        }
                        default: {
                            Object obj = rs.get(a);
                            if(obj==null)
                                map.put(a,"");
                            else
                                map.put(a, rs.get(a).toString());
                            break;
                        }
                    }
                }
                data.add(map);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return data;
    }


    @Override
    public Integer getTableDataCount(String tableName) throws Exception {
        Session s = Configuration.getSession();
        DictUtils dictUtils=new DictUtils(s);
        int c =  dictUtils.countTableData(tableName);
        s.close();
        return c;
    }

    @Override
    public void addTableData(String tableName, List<Map> columnList) throws Exception {
        Session s = Configuration.getSession();
        DictUtils dictUtils = new DictUtils(s);
        columnList  =  dictUtils.checkDataTypes(columnList);
        Pair<String,Object[]> p = dictUtils.generateInsertSQL(tableName,columnList);
        s.insert(p.first(),p.second());
        s.close();
    }
//@Override
//    public void addTableData(String tableName, List<Map> columnList) throws Exception {
//        //拼接SQL
//        String sql = buildInsertSQL(tableName, columnList);
//        Connection connection = Configuration.getSession().getConnection();
//        PreparedStatement ps = null;
//        try {
//            ps = connection.prepareStatement(sql);
//            int index = 1;
//            for (Map map : columnList) {
//                if (map.get("inputValue") != null) {
//                    //这里只要判断是不是BLOB，因为前面获取表结构的时候已经将blob、BLOB、longblob、LONGBLOB都转成了BLOB
//                    switch ((String) map.get("dataType")) {
//                        case "BLOB" :
//                            byte[] bytes = Base64.decodeBase64((String) map.get("inputValue"));
//                            Blob blob = connection.createBlob();
//                            blob.setBytes(1, bytes);
//                            ps.setBlob(index, blob);
//                            break;
//                        case "DATE":
//                            ps.setTimestamp(index, TimeUtils.stringToSqlTimestamp(String.valueOf(map.get("inputValue")), TimeUtils.timeFormat));
//                            break;
//                        default:
//                            ps.setObject(index, String.valueOf(map.get("inputValue")));
//                            break;
//                    }
//                    index++;
//                }
//            }
//            ps.executeUpdate();
//            connection.commit();
//        } catch (Exception e){
//            e.printStackTrace();
//        } finally {
//            if (ps != null) {
//                ps.close();
//            }
//        }
//    }

    @Override
    public void deleteTableData(String tableName, List<Map> columnList) throws Exception {
        Session s = Configuration.getSession();
        DictUtils dictUtils = new DictUtils(s);
        Pair<String,Object[]> p = dictUtils.generateDeleteSQL(tableName,columnList);
        String sql = p.first() +" AND rownum <= 1";
        s.delete(sql,p.second());
        s.close();
    }
//    @Override
//    public void deleteTableData(String tableName, List<Map> columnList) throws Exception {
//        String sql = Constant.DELETE_TABLE_DATA + buildDeleteSQL(tableName, columnList) + " rownum >=1 ";
//        Session s = Configuration.getSession();
//        DeleteMapper deleteMapper = s.getMapper(DeleteMapper.class);
//        deleteMapper.execute(sql);
//        s.commit();
//    }

    @Override
    public void updateTableData(String tableName, List<Map> columnList) throws Exception {
        Session s = Configuration.getSession();
        DictUtils dictUtils = new DictUtils(s);
        columnList  =  dictUtils.checkDataTypes(columnList);
        Pair<String,Object[]> pair =  dictUtils.generateUpdateSQL(tableName,columnList);
        String sql = pair.first() +" AND rownum <= 1";
        s.update(sql,pair.second());
        s.commit();
        s.close();
    }
//
//    @Override
//    public void updateTableData(String tableName, List<Map> columnList) throws Exception {
//        //拼接SQL
//        String sql = Constant.UPDATE_TABLE_DATA + buildUpdateSQL(tableName, columnList) + " rownum <= 1";
//        Connection connection = Configuration.getSession().getConnection();
//        PreparedStatement ps = null;
//        try {
//            ps = connection.prepareStatement(sql);
//            int index = 1;
//            for (Map map : columnList) {
//                Object input = map.get("inputValue");
//                Object old = map.get("oldValue");
//                if (map.get("dataType").equals("BLOB")) {
//                    if (input != null && !input.equals(old)) {
//                        byte[] bytes = Base64.decodeBase64((String) input);
//                        Blob blob = connection.createBlob();
//                        blob.setBytes(1, bytes);
//                        ps.setBlob(index, blob);
//                        index++;
//                    }
//                } else if (map.get("dataType").equals("DATE")) {
//                    if ((input != null || old != null) && (input == null || !input.equals(old))) {
//                        ps.setTimestamp(index, TimeUtils.stringToSqlTimestamp(String.valueOf(input), TimeUtils.timeFormat));
//                        index++;
//                    }
//                } else {
//                    //旧数据和新的有一个不为空，此时如果input为空，表示删除了原来的数据，否则比较新的和旧的是否相等，不等表示修改
//                    if ((input != null || old != null) && (input == null || !input.equals(old))) {
//                        ps.setObject(index, input);
//                        index++;
//                    }
//                }
//            }
//            ps.executeUpdate();
//            connection.commit();
//        } catch (Exception e){
//            e.printStackTrace();
//        }
//    }

    @Override
    public InputStream getTableBlobData(String tableName, String columnName, List<Map> columnList) throws Exception {
        String sql = null;
        Pair<String,Object[]> pair=null;
        Session s = Configuration.getSession();
        DictUtils dictUtils= new DictUtils(s);
        pair= dictUtils.generateSelectSQL(tableName,columnName,columnList);
        sql = pair.first() + " AND rownum <= 1";
        Map<String, Object> m = s.selectOne(sql,pair.second());
        Blob blob = (Blob) m.get(columnName.toUpperCase());
        InputStream inputStream= blob.getBinaryStream();
        s.close();
        return inputStream;
    }
//
//    @Override
//    public InputStream getTableBlobData(String tableName, String columnName, List<Map> columnList) throws Exception {
//        Connection connection = Configuration.getSession().getConnection();
//        PreparedStatement ps = null;
//        ResultSet rs = null;
//        InputStream inputStream;
//        //这里直接用buildDeleteSQL，没有单独写条件
//        String sql = Constant.GET_TABLE_DATA + " " +  columnName + " from " + tableName + buildDeleteSQL(tableName, columnList) + " rownum <= 1";
//        try {
//            ps = connection.prepareStatement(sql);
//            rs = ps.executeQuery();
//            rs.next();
//            Blob blob = rs.getBlob(columnName);
//            inputStream = blob.getBinaryStream();
//        } finally {
//                if (rs != null) {
//                    rs.close();
//                }
//                if (ps != null) {
//                    ps.close();
//                }
////                if (connection != null) {
////                    connection.close();
////                }
//
//        }
//        return inputStream;
//    }


    @Override
    public String buildSelectSQL(List<Map> columnList) throws Exception {
        StringBuilder value = new StringBuilder();
        for (Map map : columnList) {
            String columnName = (String)map.get("columnName");
            switch((String) map.get("dataType")) {
                case "BLOB":
                    value.append("nvl2(").append(columnName).append(",1,0) as ").append(columnName).append(", ");
                    break;
                case "DATE":
                    value.append("to_char(").append(columnName).append(", 'yyyy-mm-dd hh24:mi:ss') as ").append(columnName).append(", ");
                    break;
                default:
                    value.append(columnName).append(", ");
                    break;
            }
        }
//        java.lang.StringIndexOutOfBoundsException: String index out of range: -2
//        at java.lang.AbstractStringBuilder.delete(AbstractStringBuilder.java:756)
//        at java.lang.StringBuilder.delete(StringBuilder.java:244)
//        at cn.edu.cug.cs.gtl.docsrv.dict.dao.imp.TableDaoImpl.buildSelectSQL(TableDaoImpl.java:318)
//        需要进一步确定异常原因
        if(value.length()>1)
            value.delete(value.length() - 2, value.length());
        return value.toString();
    }

    /**
     * 这个函数构建的语句有问题，当主键为数字时，构建的SQL语句对该字段的值用引号括起来了，导致根本删除不掉数据
     * @param tableName
     * @param columnList
     * @return
     * @throws Exception
     */
    @Override
    public String buildDeleteSQL(String tableName, List<Map> columnList) throws Exception {
        int index = 0;
        StringBuilder value = new StringBuilder().append(" ").append(tableName).append(" where ");
        for (Map map : columnList) {
            //BLOB无法作为判断条件，直接忽略
            if (!map.get("dataType").equals("BLOB")) {
                if (map.get("inputValue") != null) {
                    if (map.get("dataType").equals("DATE")) {
                        value.append(map.get("columnName")).append(" = to_date('").append(map.get("inputValue")).append("', 'yyyy-mm-dd hh24:mi:ss') and ");
                    } else {
                        value.append(map.get("columnName")).append(" = '").append(map.get("inputValue")).append("' and ");
                    }
                } else {
                    value.append(map.get("columnName")).append(" is NULL and ");
                }
                index++;
            }
        }
        if (index == 0) {
            throw new Exception("无法处理只包含BLOB类型字段的表所包含的数据，因为数据无法区分");
        }

        return value.toString();
    }
    @Override
    public String buildUpdateSQL(String tableName, List<Map> columnList) throws Exception {
        StringBuilder value = new StringBuilder().append(" ").append(tableName);

        StringBuilder setValue = new StringBuilder(" set ");
        StringBuilder whereValue = new StringBuilder(" where ");

        int setIndex = 0;
        int whereIndex = 0;

        for (Map map : columnList) {
            //BLOB无法作为判断条件，直接忽略
            Object old = map.get("oldValue");
            Object input = map.get("inputValue");
            String columnName = (String) map.get("columnName");
            if (map.get("dataType").equals("BLOB")) {
                //BLOB类型不用来做判断条件
                if (old != null && input == null) {
                    //删除文件的情况
                    setValue.append(columnName).append(" = null, ");
                    setIndex++;
                } else if (input != null && !input.equals(old)) {
                    //表示新增了文件
                    setValue.append(columnName).append(" = ?, ");
                    setIndex++;
                }
                //还有就是文件没发生变化的情况，不处理
            } else if (map.get("dataType").equals("DATE")) {
                if (old != null) {
                    whereValue.append(columnName).append(" = to_date('").append(old).append("', 'yyyy-mm-dd hh24:mi:ss') and ");
                } else {
                    whereValue.append(columnName).append(" is NULL and ");
                }
                whereIndex++;
                //数据被修改了，发生了变化
                if ((input != null || old != null) && (input == null || !input.equals(old))) {
                    setValue.append(columnName).append(" = ?, ");
                    setIndex++;
                }
            } else {
                if (old != null) {
                    whereValue.append(columnName).append(" = '").append(old).append("' and ");
                } else {
                    whereValue.append(columnName).append(" is NULL and ");
                }
                whereIndex++;

                //数据被修改了，发生了变化
                if ((input != null || old != null) && (input == null || !input.equals(old))) {
                    setValue.append(columnName).append(" = ?, ");
                    setIndex++;
                }
            }
        }
        if (whereIndex == 0) {
            throw new Exception("无法更新只包含BLOB类型字段的表所包含的数据，无法区分数据");
        }
        if (setIndex == 0) {
            throw new Exception("至少需要更新一个字段");
        }

        setValue.delete(setValue.length() - 2, setValue.length());
        return value.append(setValue).append(whereValue).toString();
    }


}
